package com.sf.dlb.jpa.repository;

import com.sf.dlb.jpa.model.NewRecodInfo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * 阅读记录表 Repository
 *
 * Created by jing.tan on 2018/5/21.
 */
@Repository
public interface NewRecodRepository extends JpaRepository<NewRecodInfo, Long> {

    /**
     * 根据用户ID，新闻ID 查询
     * @param userId
     * @param newId
     * @param delFlag
     * @return
     */
    NewRecodInfo findByUserIdAndNewIdAndDelFlag(String userId, String newId, String delFlag);
    /**
     * 根据用户ID，查询
     * @param userId
     * @param delFlag
     * @return
     */
    List<NewRecodInfo> findByUserIdAndDelFlag(String userId, String delFlag);
    /**
     * 每日阅读挖宝-dlb
     * @param userId
     * @param time
     * @param delflag
     * @return
     */
    @Query(value = "select sum(cast(a.get_number as decimal(18,3))) from new_recod a where a.user_id=?1 and DATE_FORMAT(a.start_time,'%Y-%m-%d')=?2 and a.get_state=?3 and a.del_flag=?4 ",nativeQuery = true)
    Double getEveryDayReadGetdlb(String userId, String time, String getState, String delflag);
    /**
     * 每日阅读数量-状态(有效)
     * @param userId
     * @param time
     * @param delflag
     * @param getState
     * @return
     */
    @Query(value = "select count(*) from new_recod a where a.user_id=?1 and DATE_FORMAT(a.start_time,'%Y-%m-%d')=?2 and a.del_flag=?3 and a.get_state=?4",nativeQuery = true)
    Integer findByEveryDayReadNumber(String userId, String time, String delflag, String getState);
    /**
     * 每日阅读数量
     * @param userId
     * @param time
     * @param delflag
     * @return
     */
    @Query(value = "select count(*) from new_recod a where a.user_id=?1 and DATE_FORMAT(a.start_time,'%Y-%m-%d')=?2 and a.del_flag=?3",nativeQuery = true)
    Integer findByEveryDayReadNumber(String userId, String time, String delflag);
    /**
     * 每日阅读时长
     * @param userId
     * @param time
     * @param delflag
     * @return
     */
    @Query(value = "select sum(a.read_time_long) from new_recod a where a.user_id=?1 and DATE_FORMAT(a.start_time,'%Y-%m-%d')=?2 and a.del_flag=?3 and a.get_state=?4 ",nativeQuery = true)
    Integer findByEveryDayReadTime(String userId, String time, String delflag, String getState);
}